﻿CREATE FUNCTION [dbo].[PaymentSchedule78](
	@AmountFinanced float,
	@FinanceCharge float,
	@Payment float,
	@NumberOfPayments int,
	@PaymentsPerYear tinyint,
	@PaymentStart int,
	@PaymentEnd int
) RETURNS @AmrtTbl TABLE(PaymentNumber int IDENTITY,Mos int,ToInterest numeric(18,2),ToPrinciple numeric(18,2),RemainingPrinciple numeric(18,2),FinanceCharge numeric(18,2)) AS
BEGIN

DECLARE @Pmt int SET @Pmt = 1
DECLARE @ToInterest float,@ToPrinciple float,@FC float
DECLARE @MagicNumber float SET @MagicNumber = @NumberOfPayments*(@NumberOfPayments+1)/2

IF ISNULL(@PaymentStart,0) <= 0 SET @PaymentStart = 1
IF ISNULL(@PaymentEnd,0) <= 0 SET @PaymentEnd = 1000000

WHILE @Pmt <= @NumberOfPayments BEGIN
	SET @ToInterest = @FinanceCharge*(@NumberOfPayments-@Pmt+1)/@MagicNumber
	SET @FC = ISNULL(@FC,0) + @ToInterest
	SET @ToPrinciple = @Payment - @ToInterest
	SET @AmountFinanced = @AmountFinanced - @ToPrinciple
	IF @Pmt >= @PaymentStart AND @Pmt <= @PaymentEnd
		INSERT INTO @AmrtTbl VALUES(@Pmt/@PaymentsPerYear/12.0,@ToInterest,@ToPrinciple,@AmountFinanced,@FC)
	SET @Pmt = @Pmt + 1
END

RETURN
END



